package com.wwg.warehouse.data.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import com.wwg.warehouse.data.DBEntity

@Dao
interface BookmarkDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(vararg book: DBEntity)

    @Query("""
        WITH RECURSIVE temporary_sub_tree_view(name, path, parentName, parentPath, content, sortOrder, level) AS 
        (SELECT name, path, parentName, parentPath, content, sortOrder, 0
        FROM bookmarks WHERE name=:name AND path=:path
        UNION ALL
        SELECT b.name, b.path, b.parentName, b.parentPath, b.content, b.sortOrder, level+1 
        FROM temporary_sub_tree_view t, bookmarks b
        WHERE b.parentName = t.name AND b.parentPath = t.path AND level<:deep)
        SELECT * FROM temporary_sub_tree_view order by path
    """)
    fun querySubTree(name: String, path: String, deep: Int): List<DBEntity>

//    @Query("""
//        WITH RECURSIVE route_view(name, path, parentName, parentPath, content, sortOrder, level) AS
//        (SELECT name, path, parentName, parentPath, content, sortOrder, 0
//        FROM bookmarks WHERE name=:name AND path=:path
//        UNION ALL
//        SELECT (route_view.name || :connector || b.name), b.path, b.parentName, b.parentPath, b.content, b.sortOrder, level+1
//        FROM bookmarks b INNER JOIN route_view
//        ON route_view.parentName=b.name AND route_view.parentPath=b.path)
//        SELECT * FROM route_view WHERE level=(SELECT max(level) FROM route_view)
//    """)
//    fun queryRoute(name: String, path: String, connector: String)

//    @Query("""
//        WITH RECURSIVE route_view(name, path, parentName, parentPath, content, sortOrder, level) AS
//        (SELECT name, path, parentName, parentPath, content, sortOrder, 0
//        FROM bookmarks WHERE parentName=:name AND parentPath=:path
//        UNION ALL
//        SELECT (route_view.name || :connector || b.name), b.path, b.parentName, b.parentPath, b.content, b.sortOrder, level+1
//        FROM bookmarks b INNER JOIN route_view
//        ON route_view.parentName=b.name AND route_view.parentPath=b.path)
//        SELECT * FROM route_view WHERE level=(SELECT max(level) FROM route_view)
//    """)
//    fun queryParentRoute(name: String, path: String, connector: String)

    @Query("SELECT * FROM bookmarks")
    fun queryAll(): List<DBEntity>
}